var express = require('express');
var axios = require('axios');
var mysql = require('mysql');
const os = require('os');
var dateFormat = require('./dateFormat');
let { host, port, user, password, database, multipleStatements } = require('./config');

function getIP() {
    //获取本机ip
    let getIPAdress = function () {
        var interfaces = os.networkInterfaces();
        // console.log('', interfaces)
        for (var devName in interfaces) {
            var iface = interfaces[devName];
            for (var i = 0; i < iface.length; i++) {
                var alias = iface[i];
                if (alias.family === 'IPv4' && alias.address !== '127.0.0.1' && !alias.internal) {
                    return alias.address;
                }
            }
        }
    }
    let ip = getIPAdress();
    console.log(ip);// 192.168.0.4
    return ip;
}

// mysqljs 连接 mysql数据库
let connection = mysql.createConnection({
    host: getIP() == '192.168.0.4' ? '127.0.0.1' : host, // mysql所在的主机，本地的话就是 127.0.0.1 或者 localhost, 如果数据库在服务器上，就写服务器的ip
    port,
    user, // mysql的用户名
    password, // mysql的密码
    multipleStatements, // 多语句执行
    database // 你要连接那个数据库
})

// 连接 mysql
connection.connect(async err => {
    // err代表失败
    if (err) {
        console.log("connectionMysql.js -  数据库初始化失败");
        console.log('connectionMysql.js -  ', err.message)
        // 断开链接
        connection.end();
        console.log('connectionMysql.js -  关闭数据库链接',)
        // 没有对应的mysql数据库
        if (err.message.includes('Unknown database')) {
            console.log('connectionMysql.js -  没有对应的mysql数据库，执行创建数据库的命令！！！')
            // connection = await CreateDatabase()
        }
    } else {
        console.log("connectionMysql.js -  数据库初始化成功");
    }
})


var app = express();
var bodyParser = require('body-parser');//用于req.body获取值的
// 处理静态资源
app.use(express.static('public'))
// 处理参数
app.use(bodyParser.json())
app.use(bodyParser.urlencoded({ extended: false }))
//  ------------------
//  这个文件时在学习vue的前端交互时用于测试的
// -------------------

app.get('*', function (req, res, next) {
    res.header("Access-Control-Allow-Origin", "*");
    res.header("Access-Control-Allow-Methods", "PUT,GET,POST,DELETE,OPTIONS");
    res.header("Access-Control-Allow-Headers", "X-Requested-With");
    res.header("Access-Control-Allow-hEADERS", "Content-Type");
    next();
});

app.get('/', function (req, res) {
    res.send('hello world');
});

app.get('/setData/:issueNo/:one/:two/:three/:four/:five/', function (req, res) {
    // console.log('', req.params)
    let data = [];
    Object.keys(req.params).forEach((item) => {
        data.push(Number(req.params[item]));
    })
    data.push(new Date());
    connection.query(`INSERT INTO issue_data(issueNo,one,two,three,four,five,createDate) VALUES (?,?,?,?,?,?,?);`, data, function (error, results, fields) {
        if (error) {
            res.send({
                success: false,
                results
            })
        } else {
            res.send({
                success: true,
                results
            })
        }
    });
});

// 获取指定数量的最新数据
app.get('/trend/:pageSize', function (req, res) {
    let pageSize = req.params['pageSize'] || 120
    axios.post('https://5361v.com/frontend/v1/lottery/trend', {
        lotteryId: 16,
        pageSize: pageSize
    })
        .then(function (response) {
            let data = response.data.data.data;
            let list = data.list;
            // console.log('', list)
            console.log('', list.length)
            let insertList = '';

            list.forEach((item) => {
                // console.log('=== ', item)
                let info = item.resultInfo.split(',')
                insertList += `INSERT INTO issue_data(issueNo,one,two,three,four,five,createDate) VALUES (${Number(String(item.issue).substr(4))},${Number(info[0])},${Number(info[1])},${Number(info[2])},${Number(info[3])},${Number(info[4])},"${dateFormat(new Date(), 'Y-m-d H:i:s')}");`
            })

            connection.query(insertList, function (error, results, fields) {
                if (error) {
                    console.log('', error)
                } else {
                    // console.log('', results)
                    axios.get('http://127.0.0.1:3002/RemoveDuplicates', {})
                        .then(function (response) {
                            console.log('RemoveDuplicates success');
                        })
                        .catch(function (error) {
                            console.log(error);
                        });
                    res.send({
                        success: false,
                        results
                    })
                }
            });
        })
        .catch(function (error) {
            console.log(error);
        });
});

// 获取指定数量的最后添加的issueNo数据
app.get('/getLimit/:num', function (req, res) {
    let num = req.params['num'] || 120
    getLimit(num, res);
});

// 获取指定数量的最后添加的数据
app.get('/getLimitData/:num', function (req, res) {
    let num = req.params['num'] || 120
    getLimitData(num, res);
});

// 执行sql语句
app.get('/exeQuery', function (req, res) {
  // console.log('=====', req.query.sql)
  let sql = req.query.sql
  connection.query(`${sql}`, function (error, results, fields) {
    if (error) {
      res && (res.send && res.send([]) || res([]))
    } else {
      res && (res.send && res.send(results) || res(results))
    }
  });
});

// 获取所有数据
app.get('/getAll', function (req, res) {
    getAll(res);
});

// 去重。删除相同期号的数据
app.get('/RemoveDuplicates', function (req, res) {
    RemoveDuplicates(res)
});

app.listen(3002, function () {
    console.log('访问```：127.0.0.1:3002');
});

/* ================================ */
/* ================================ */
/* ================================ */
/* ================================ */

// 获取指定数量的最后添加的issueNo数据
function getLimit(num, res) {
    connection.query(`SELECT distinct * FROM issue_data order by issueNo DESC limit ${num || 100}`, function (error, results, fields) {
        if (error) {
            res && (res.send && res.send([]) || res([]))
        } else {
            let data = [];
            results.forEach((item) => {
                data.push(item['issueNo']);
            })
            res && (res.send && res.send(data) || res(data))
        }
    });
}

// 获取指定数量的最后添加的数据
function getLimitData(num, res) {
    connection.query(`SELECT distinct * FROM issue_data order by issueNo DESC limit ${num || 100}`, function (error, results, fields) {
        if (error) {
            res && (res.send && res.send([]) || res([]))
        } else {
            res && (res.send && res.send(results) || res(results))
        }
    });
}

// 获取所有数据
function getAll(res) {
    connection.query(`SELECT distinct * FROM issue_data order by issueNo DESC`, function (error, results, fields) {
        if (error) {
            res && (res.send && res.send([]) || res([]))
        } else {
            res && (res.send && res.send(results.reverse()) || res(results.reverse()))
        }
    });
}

// 去重。删除相同期号的数据
function RemoveDuplicates(res) {
    connection.query(`SELECT distinct * FROM issue_data order by issueNo DESC`, function (error, results, fields) {
        if (error) {
            res && (res.send && res.send([]) || res([]))
        } else {
            let countData = [];
            let exeString = "";
            results.forEach((item) => {
                if (!countData.includes(item.issueNo)) {
                    countData.push(item.issueNo);
                } else {
                    exeString += `delete from issue_data where id=${item.id};`
                }
            })
            if (exeString) {
                connection.query(exeString, function (error3, results3, fields3) {
                    if (error) {
                        console.log('', error)
                    } else {
                        // console.log('成功删除：', results3)
                    }
                });
            } else console.log('not Duplicates data',)
            res && (res.send && res.send(results) || res(results))
        }
    });
}

setTimeout(() => {
    axios.get('http://127.0.0.1:3002/trend/10000', {})
        .then(function (response) {
            console.log('RemoveDuplicates success');
        })
        .catch(function (error) {
            console.log(error);
        });
}, 2000);

setInterval(() => {
    RemoveDuplicates()
}, 1000 * 60 * 30);

setInterval(() => {
    let pageSize = 10
    axios.post('https://5361v.com/frontend/v1/lottery/trend', {
        lotteryId: 16,
        pageSize: pageSize
    })
        .then(function (response) {
            let data = response.data.data.data;
            let list = data.list;
            // console.log('', list)
            console.log('', list.length)
            let insertList = '';

            getLimit(pageSize + 10, (issueNoList) => {
                list.forEach((item) => {
                    if (issueNoList.includes(Number(String(item.issue).substr(4)))) return;
                    // console.log('=== ', item)
                    let info = item.resultInfo.split(',')
                    insertList += `INSERT INTO issue_data(issueNo,one,two,three,four,five,createDate) VALUES (${Number(String(item.issue).substr(4))},${Number(info[0])},${Number(info[1])},${Number(info[2])},${Number(info[3])},${Number(info[4])},"${dateFormat(new Date(), 'Y-m-d H:i:s')}");`
                })

                insertList && connection.query(insertList, function (error, results, fields) {
                    if (error) {
                        console.log('', error)
                    } else {
                        console.log('', insertList)
                        console.log({
                            success: false,
                            results
                        })
                    }
                });
            })
        })
        .catch(function (error) {
            console.log(error);
        });
}, 1000 * 25);